﻿//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////  LIBRERIA: SIMA.CORE                                                                                             ////
////  DESARROLLADO POR JUAN CARLOS VEGA NEIRA                                                                         ////
////  UNIVERSIDAD RICARDO PALMA                                                                                       ////
////  CURSO DE TALLER DE GERENTCIA DE PROYECTOS                                                                       ////
////  SEMESTRE 2010 - II                                                                                              ////
////  SEPTIEMBRE, 2010                                                                                                ////
////  TODOS LOS DERECHOS RESERVADOS                                                                                   ////
////  VERSIÓN v1.0                                                                                                    ////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Text;
using System.Reflection;
using System.Configuration;

using SIMA.Core.Attributes;
using SIMA.Core.Utils.Security;

namespace SIMA.Core.Utils
{
    public static class SqlUtils<T>
    {

        public static string InsertQueryFormater(T item)
        {
            Type classType = typeof(T);
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append("INSERT INTO ");
            strBuilder.Append(classType.Name);
            strBuilder.Append(" values(");
            PropertyInfo[] properties = classType.GetProperties();
            for (int i = 0; i <= properties.Length - 1; i++)
            {
                ValidatorUtils<T>.ValidationRules(properties[i], item);
                if (i <= properties.Length - 5)
                {
                    if (Attribute.GetCustomAttributes(properties[i], typeof(PrimaryKey)).Length > 0)
                    {
                        Attribute attr = Attribute.GetCustomAttribute(properties[i], typeof(PrimaryKey));
                        PrimaryKey pkey = (PrimaryKey)attr;
                        if (!pkey.Autogenerated)
                        {
                            strBuilder.Append(FormatUtils<T>.FieldFormater(properties[i], item));
                            strBuilder.Append(",");
                        }
                    }
                    else
                    {
                        if (properties[i].Name != "PcUsuario" && properties[i].Name!="Operacion" && properties[i].Name!="FechaTransaccion")
                        {
                            if (Attribute.GetCustomAttribute(properties[i], typeof(Encrypted)) != null)
                            {
                                Attribute attr = Attribute.GetCustomAttribute(properties[i], typeof(Encrypted));
                                properties[i].SetValue(item, SecurityUtils.Encrypt(properties[i].GetValue(item, null).ToString()), null);
                            }
                            strBuilder.Append(FormatUtils<T>.FieldFormater(properties[i], item));
                            strBuilder.Append(",");
                        }
                    }
                }
                else
                {
                    strBuilder.Append(FormatUtils<T>.FieldFormater(properties[i], item));
                }
            }
            strBuilder.Append(")");
            return strBuilder.ToString();
        }

        public static string UpdateQueryFormater(T item)
        {
            Type classType = typeof(T);
            int contk = 0, contf = 0;
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append("UPDATE ");
            strBuilder.Append(classType.Name);
            strBuilder.Append(" SET ");

            StringBuilder cluaseBuilder = new StringBuilder();

            foreach (PropertyInfo property in classType.GetProperties())
            {
                if (property.Name != "PcUsuario" && property.Name != "FechaTransaccion" && property.Name != "Operacion")
                {
                    ValidatorUtils<T>.ValidationRules(property, item);

                    if (Attribute.GetCustomAttribute(property, typeof(DataField)) != null)
                    {
                        if (contf > 0)
                        {
                            strBuilder.Append(",");
                        }
                        strBuilder.Append(((DataField)Attribute.GetCustomAttribute(property, typeof(DataField))).Name);
                        strBuilder.Append("=");
                        strBuilder.Append(FormatUtils<T>.FieldFormater(property, item));
                        contf++;

                    }

                    if (Attribute.GetCustomAttribute(property, typeof(PrimaryKey)) != null)
                    {
                        if (contk < 0)
                        {
                            cluaseBuilder.Append(" AND ");
                        }
                        cluaseBuilder.Append(((PrimaryKey)Attribute.GetCustomAttribute(property, typeof(PrimaryKey))).PrimaryKeyName);
                        cluaseBuilder.Append("=");
                        cluaseBuilder.Append(FormatUtils<T>.FieldFormater(property, item));
                        contk++;
                    }
                }
            }

            strBuilder.Append(" WHERE ");
            strBuilder.Append(cluaseBuilder.ToString());
            return strBuilder.ToString();
        }

        public static string SelectAllQueryFormater()
        {
            Type classType = typeof(T);
            string query = string.Empty;

            PropertyInfo[] properties = classType.GetProperties();

            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append("SELECT ");

            for (int i = 0; i <= properties.Length - 4; i++)
            {
                if (i <= properties.Length - 5)
                {
                    Attribute[] attrs = Attribute.GetCustomAttributes(properties[i]);
                    if (attrs.Length > 0)
                    {
                        foreach (Attribute attr in attrs)
                        {
                            if (attr is PrimaryKey)
                            {
                                PrimaryKey pk = (PrimaryKey)attr;
                                strBuilder.Append(pk.PrimaryKeyName);
                            }
                            if (attr is DataField)
                            {
                                DataField dt = (DataField)attr;
                                strBuilder.Append(dt.Name);
                            }
                            
                        }

                    }
                    else
                    {
                        strBuilder.Append(properties[i].Name);
                    }
                    strBuilder.Append(",");

                }
                else
                {
                    strBuilder.Append(properties[i].Name);
                }                
            }

            strBuilder.Append(" FROM ");
            if (Attribute.GetCustomAttribute(classType, typeof(Table)) != null)
            {
                Table table = (Table)Attribute.GetCustomAttribute(classType, typeof(Table));
                strBuilder.Append(table.TableName);
            }
            else
            {
                strBuilder.Append(classType.Name);
            }

            query = strBuilder.ToString();

            return query;
        }

        public static string SelectByQueryFormater(string queryClause)
        {
            Type classType = typeof(T);
            string query = string.Empty;

            PropertyInfo[] properties = classType.GetProperties();

            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append("SELECT ");

            for (int i = 0; i <= properties.Length - 4; i++)
            {
                if (i <= properties.Length - 5)
                {
                    Attribute[] attrs = Attribute.GetCustomAttributes(properties[i]);
                    if (attrs.Length > 0)
                    {
                        foreach (Attribute attr in attrs)
                        {
                            if (attr is PrimaryKey)
                            {
                                PrimaryKey pk = (PrimaryKey)attr;
                                strBuilder.Append(pk.PrimaryKeyName);
                            }
                            if (attr is DataField)
                            {
                                DataField dt = (DataField)attr;
                                strBuilder.Append(dt.Name);
                            }
                        }
                    }
                    else
                    {
                        strBuilder.Append(properties[i].Name);
                    }
                    strBuilder.Append(",");

                }
                else
                {
                    strBuilder.Append(properties[i].Name);
                }
            }

            strBuilder.Append(" FROM ");
            if (Attribute.GetCustomAttribute(classType, typeof(Table)) != null)
            {
                Table table = (Table)Attribute.GetCustomAttribute(classType, typeof(Table));
                strBuilder.Append(table.TableName);
            }
            else
            {
                strBuilder.Append(classType.Name);
            }

            strBuilder.Append(" where ");
            strBuilder.Append(queryClause);

            query = strBuilder.ToString();

            return query;
        }

        public static string DeleteQueryFormater(T item)
        {
            Type classType = typeof(T);
            string query = string.Empty;

            StringBuilder strBuilder = new StringBuilder();

            strBuilder.Append("DELETE FROM ");
            Attribute attr = Attribute.GetCustomAttribute(classType, typeof(Table));
            if (attr != null)
            {
                Table table = (Table)attr;
                strBuilder.Append(table.TableName);
            }
            else
            {
                strBuilder.Append(classType.Name);
            }

            strBuilder.Append(" WHERE ");

            foreach (PropertyInfo property in classType.GetProperties())
            {
                Attribute attr2 = Attribute.GetCustomAttribute(property, typeof(PrimaryKey));
                if (attr2 != null)
                {
                    PrimaryKey pk = (PrimaryKey)attr2;
                    strBuilder.Append(pk.PrimaryKeyName);
                    strBuilder.Append("=");
                    strBuilder.Append(FormatUtils<T>.FieldFormater(property, item));
                    strBuilder.Append(" and ");
                }
            }

            strBuilder.Remove(strBuilder.Length - 5, 5);

            query = strBuilder.ToString();

            return query;
        }

    }
}
